package com.scorpio.scbasics.bizuser.mapper;
import java.util.List;
import com.scorpio.common.dao.BaseDao;
import com.scorpio.scbasics.bizgoddessauth.dto.BizGoddessAuthDTO;
import com.scorpio.scbasics.bizlocation.domain.BizLocationEntity;
import com.scorpio.scbasics.bizlocation.dto.BizLocationDTO;
import com.scorpio.scbasics.bizmemorandum.domain.BizMemorandumEntity;
import com.scorpio.scbasics.bizmemorandum.dto.BizMemorandumDTO;
import com.scorpio.scbasics.bizsms.dto.BizSmsDTO;
import com.scorpio.scbasics.bizuser.domain.BizUserEntity;
import com.scorpio.scbasics.bizuser.dto.BizUserDTO;
import com.scorpio.scbasics.bizuseronline.dto.BizUserOnlineDTO;
import com.scorpio.scbasics.bizviprecord.dto.BizVipRecordDTO;
import org.apache.ibatis.annotations.*;
import org.springframework.beans.factory.annotation.Autowired;

/**
 * 【请填写功能名称】Mapper接口
 *
 * @author scorpio
 * @date 2020-10-13
 */
@Mapper
public interface BizUserMapper extends BaseDao<BizUserEntity> {

    @Select("select u.*,o.occupat_name,h.height_name,w.weight_name from biz_user u \n" +
            " left join biz_occupations o on u.occupations_id=o.id \n" +
            " left join biz_height h on u.height_id=h.id \n" +
            " left join biz_weight w on u.weight_id=w.id\n" +
            "  ${sql}")
    BizUserDTO getMyInfo(@Param("sql") String sql);

    @Select("select u.*,o.occupat_name,h.height_name,w.weight_name,m.memo_name,\n" +
            "                                case when l.lng is null then '0' else l.lng end as lng,case when l.lat is null then '0' else l.lat end as lat,case when l.city is null then '未知' else l.city end as location_city, \n" +
            "                                round(6378.138 * 2 * asin(sqrt(power(sin((#{lat} * pi() / 180 - (case when l.lat is null then '0' else l.lat end) * pi() / 180) / 2),2) + cos(#{lat} * pi() / 180) * cos((case when l.lat is null then '0' else l.lat end) * pi() / 180) * power(sin((#{lng} * pi()/180 - (case when l.lng is null then '0' else l.lng end) * pi() / 180) / 2),2))),3) as distance from biz_user u \n" +
            "                                left join biz_occupations o on u.occupations_id=o.id \n" +
            "                                left join biz_height h on u.height_id=h.id \n" +
            "                                left join biz_weight w on u.weight_id=w.id \n" +
            "                                left join biz_memorandum m on u.id = m.to_user_id and m.user_id=#{user_id} \n" +
            "                                left join biz_location l on u.id = l.user_id \n" +
            "                                where u.id=#{to_user_id}")
    BizUserDTO GetUser(@Param("user_id")int user_id, @Param("to_user_id")int to_user_id, @Param("lat")String lat, @Param("lng")String lng);

    @Select("select pwd from biz_user where id=#{user_id}")
    String GetPwdByID(@Param("user_id")int user_id);

    @Update("update biz_user set pwd=#{newPwd},lastupdate_time=default ${sql}")
    int ChangePwd(@Param("sql")String sql, @Param("newPwd")String newPwd);

    @Update("update biz_user set mobile=#{mobile},lastupdate_time=default where id=#{user_id}")
    int ChangeMobile(@Param("user_id")int user_id, @Param("mobile")String mobile);

    @Update("update biz_user set is_true=#{is_true},best_frame=#{best_frame},lastupdate_time=default where id=#{user_id}")
    int ModifyIsTrue(Integer user_id,Integer is_true,String best_frame);

    @Update("update biz_user set is_true=#{auth_status},is_goddess=#{goddess_status},lastupdate_time=default where id=#{user_id}")
    int ModifyAuthStatus(@Param("user_id")int user_id, @Param("auth_status")int auth_status, @Param("goddess_status")int goddess_status);

    @Update("update biz_user set best_frame=#{best_frame},lastupdate_time=default where id=#{user_id}")
    int ModifyAuthBestFrame(@Param("user_id")int user_id, @Param("best_frame")String best_frame);


    @Select("if exists (select * from biz_memorandum where user_id=#{m.user_id} and to_user_id=#{m.to_user_id})\n" +
            "                                update biz_memorandum set memo_name=#{m.memo_name},describe=#{m.describe},lastupdate_time=default where user_id=#{m.user_id} and to_user_id=#{m.to_user_id}\n" +
            "                            else \n" +
            "                                insert into biz_memorandum (user_id,to_user_id,memo_name,describe,create_time,lastupdate_time) values (#{m.user_id},#{m.to_user_id},#{m.memo_name},#{m.describe},default,default)")
    int SetMemorandum(@Param("m")BizMemorandumDTO m);


    @Select("select * from biz_memorandum where user_id=#{user_id} and to_user_id=#{to_user_id}")
    BizMemorandumDTO GetMemorandum(@Param("user_id")int user_id, @Param("to_user_id")int to_user_id);

    @Update("update biz_user set online=#{online},last_online_time=#{last_online_time} where id=#{user_id}")
    int ChangeOnLine(@Param("user_id")int user_id, @Param("online")int online, @Param("last_online_time")String last_online_time);

    @Select("select u.id to_user_id,case when m.memo_name is null or m.memo_name='' then u.nickname else m.memo_name end as nickname,u.is_lianmai from biz_user u \n" +
            "                                left join biz_memorandum m on u.id = m.to_user_id and m.user_id=#{user_id} where u.id in (${to_user_ids})")
    BizUserDTO GetNickNameGroup(@Param("user_id")int user_id, @Param("to_user_ids")String to_user_ids);

    @Update("update biz_user set is_lianmai=#{is_lianmai},lastupdate_time=default where id=#{user_id}")
    int ModifyLianMai(@Param("user_id")int user_id, @Param("is_lianmai")int is_lianmai);

    @Update("update biz_user set is_valid=#{is_hide},lastupdate_time=default where id=#{user_id}")
    int ModifyHide(@Param("user_id")int user_id, @Param("is_hide")int is_hide);

    @Update("update biz_user set online=#{is_secrecy},lastupdate_time=default where id=#{user_id}")
    int ModifyOnline(@Param("user_id")int user_id, @Param("is_secrecy")int is_secrecy);

    @Update("update biz_user set hide_distance=#{hide_distance},lastupdate_time=default where id=#{user_id}")
    int ModifyHideDistance(@Param("user_id")int user_id, @Param("hide_distance")int hide_distance);

    @Update("update biz_user set hide_wechat=#{hide_wechat},lastupdate_time=default where id=#{user_id}")
    int ModifyHideWechat(@Param("user_id")int user_id, @Param("hide_wechat")int hide_wechat);

    @Select("select count(1) from biz_location where user_id=#{user_id}")
    int ExistLocation(@Param("user_id")int user_id);

    @Insert("insert into biz_location (user_id,lng,lat,country,province,city,district,formatted_address,create_time,lastupdate_time) values (#{location.user_id},#{location.lng},#{location.lat},#{location.country},#{location.province},#{location.city},#{location.district},#{location.formatted_address},default,default)")
    int AddUserLocation(@Param("location")BizLocationDTO location);

    @Update("update biz_location set lng=#{location.lng},lat=#{location.lat},country=#{location.country},province=#{location.province}, " +
            " city=#{location.city},district=#{location.district},formatted_address=#{location.formatted_address},lastupdate_time=default " +
            " where user_id=#{location.user_id}")
    int ModifyUserLocation(@Param("location")BizLocationDTO location);

    @Update("if exists (select * from biz_location where user_id=#{location.user_id})\n" +
            "                                update biz_location set lng=#{location.lng},lat=#{location.lat},country=#{location.country},province=#{location.province},city=#{location.city},district=#{location.district},formatted_address=#{location.formatted_address},lastupdate_time=default where user_id=#{location.user_id}\n" +
            "                            else \n" +
            "                                insert into biz_location (user_id,lng,lat,country,province,city,district,formatted_address,create_time,lastupdate_time) values (#{location.user_id},#{location.lng},#{location.lat},#{location.country},#{location.province},#{location.city},#{location.district},#{location.formatted_address},default,default)")
    int SetUserLocation(@Param("location")BizLocationDTO location);

@Update("if exists (select * from biz_goddess_auth where user_id=#{ga.user_id})\n"+
        "                                update biz_goddess_auth set url=#{ga.url},status=#{ga.status},remarks=#{ga.remarks},lastupdate_time=default where user_id=#{ga.user_id}\n"+
        "                            else \n"+
        "                                insert into biz_goddess_auth (user_id,url,status,remarks,create_time,lastupdate_time) values (#{ga.user_id},#{ga.url},#{ga.status},#{ga.remarks},default,default)")
    int SetGoddessAuth(@Param("ga")BizGoddessAuthDTO ga);


    @Select("select * from biz_goddess_auth where user_id=#{user_id}")
    BizGoddessAuthDTO GetGoddessAuth(@Param("user_id")int user_id);

    @Select("select count(1) from (select user_id,to_user_id,type from biz_user_count \n" +
            "                                                    where type=#{type} and user_id=#{user_id} and convert(varchar(10),getdate(),23)=convert(varchar(10),create_time,23)\n" +
            "                                                    group by user_id,to_user_id,type) tmp")
    int GetUserCount(@Param("user_id")int user_id, @Param("type")int type);


    @Select("select count(1) from (select user_id,to_user_id,type from biz_user_count \n" +
            "                                                    where type=#{type} and user_id=#{user_id} and to_user_id=#{to_user_id} and convert(varchar(10),getdate(),23)=convert(varchar(10),create_time,23)\n" +
            "                                                    group by user_id,to_user_id,type) tmp")
    int GetUserCount(@Param("user_id")int user_id, @Param("to_user_id")int to_user_id, @Param("type")int type);


    @Insert("insert into biz_user_count (user_id,to_user_id,type,create_time) values (#{user_id},#{to_user_id},#{type},default)")
    int AddUserCount(@Param("user_id")int user_id, @Param("to_user_id")int to_user_id, @Param("type")int type);


    @Insert("insert into biz_sms (mobile,verify_code,type,create_time) values (#{mobile},#{verify_code},#{type},default)")
    int AddSms(@Param("mobile")String mobile, @Param("verify_code")int verify_code, @Param("type")int type);

    @Select("select * from biz_sms where mobile=#{mobile} and type=#{type} order by create_time desc")
    BizSmsDTO GetSmsByMobile(@Param("mobile")String mobile, @Param("type")int type);

    @Select("select * from biz_vip_record where user_id=#{user_id}")
    BizVipRecordDTO GetVipRecord(@Param("user_id")int user_id);


    @Delete("delete from biz_vip_record where user_id=#{user_id}")
    int RemoveVipRecord(@Param("user_id")int user_id);


    @Update("update biz_user set is_vip=0,lastupdate_time=default where id=#{user_id}")
    int ModifyIsVip(@Param("user_id")int user_id);


    @Insert("insert into biz_user_online \n" +
            "                               (\n" +
            "\t\t                            user_id\n" +
            "                                   ,operate\n" +
            "                                   ,im_action\n" +
            "                                   ,time\n" +
            "\t\t                       )\n" +
            "                            values\n" +
            "                                (\n" +
            "\t\t                            #{uo.user_id}\n" +
            "                                   ,#{uo.operate}\n" +
            "                                   ,#{uo.im_action}\n" +
            "                                   ,default\n" +
            "\t\t                       )")
    int AddUserOnlineTime(@Param("uo")BizUserOnlineDTO uo);



    @Select("select * from (\n" +
            "                                select row_number() over(order by u.create_time desc) rn,count(*) over() as total,u.*,o.occupat_name,\n" +
            "                                case when l.city is null then '未知' else l.city end as location_city,l.formatted_address \n" +
            "                                from biz_user u \n" +
            "                                left join biz_occupations o on u.occupations_id = o.id \n" +
            "                                left join biz_location l on u.id = l.user_id \n" +
            "                                where 1=1 and u.status=4  ${condition} \n" +
            "                            ) t1 where 1=1 ${paging}")
    BizUserDTO QueryList(@Param("paging")String condition, @Param("paging")String paging);


    @Select("select u.*,o.occupat_name,h.height_name,w.weight_name,\n" +
            "                                case when l.city is null then '未知' else l.city end as location_city,l.formatted_address\n" +
            "                                from biz_user u \n" +
            "                                left join biz_occupations o on u.occupations_id=o.id \n" +
            "                                left join biz_height h on u.height_id=h.id \n" +
            "                                left join biz_weight w on u.weight_id=w.id \n" +
            "                                left join biz_location l on u.id = l.user_id \n" +
            "                                where u.id=#{user_id} ")
    BizUserDTO GetUserDetail(@Param("user_id")int user_id);


    @Select("select * from ( " +
            " select  " +
            " row_number() over(order by u.create_time desc) rn, " +
            " count(*) over() as total, " +
            " u.* " +
            " from biz_user u  " +
            " where 1=1 and u.status<>4  ${condition}  " +
            "                            ) t1 where 1=1 ${paging}")
    BizUserDTO QueryUnRegList(@Param("condition")String condition, @Param("paging")String paging);


    @Update("update biz_user set is_valid=#{is_valid} where id=#{user_id}")
    int ModifyAccountStatus(@Param("user_id") int user_id, @Param("is_valid")int is_valid);


    @Select("select * from (\n" +
            " select row_number() over(order by ga.create_time desc) rn,count(*) over() as total,\n" +
            " ga.*,u.mobile,u.picture,u.nickname,u.sex,u.age,u.constellation,u.birthday,u.wechat,u.is_valid,u.create_time reg_time,o.occupat_name\n" +
            " from biz_goddess_auth ga \n" +
            " left join biz_user u on ga.user_id = u.id \n" +
            " left join biz_occupations o on u.occupations_id = o.id\n" +
            " where u.is_valid<>0 and u.status=4 ${condition} " +
            "                            ) t1 where 1=1 ${paging}")
    BizGoddessAuthDTO GoddessAuthList(String condition, String paging);

    @Select("select ga.id auth_id,\n" +
            " ga.url,\n" +
            " ga.user_id,\n" +
            " ga.remarks auth_remarks,\n" +
            " ga.status auth_status,\n" +
            " ga.create_time auth_submit_time,\n" +
            " u.*,\n" +
            " o.occupat_name,\n" +
            " h.height_name,\n" +
            " w.weight_name,\n" +
            " case when l.city is null then '未知' else l.city end as location_city,\n" +
            " l.formatted_address \n" +
            " from biz_goddess_auth ga\n" +
            " left join biz_user u on ga.user_id=u.id\n" +
            " left join biz_occupations o on u.occupations_id=o.id \n" +
            " left join biz_height h on u.height_id=h.id \n" +
            " left join biz_weight w on u.weight_id=w.id\n" +
            " left join biz_location l on u.id = l.user_id \n" +
            " where ga.id=#{auth_id}")
    BizUserDTO GetGoddessAuthDetail(@Param("auth_id") int auth_id);


    @Update("update biz_goddess_auth set status=#{status},remarks=#{remarks} where id=#{auth_id}")
    int modifyAuthStatus(@Param("auth_id")int auth_id, @Param("status")int status,@Param("remarks")String remarks);


    @Update("update biz_user set is_goddess=#{is_goddess} where id=#{user_id}")
    int modifyUserGoddessStatus(@Param("user_id")int user_id,@Param("is_goddess")int is_goddess);

    @Select("select * from biz_user where status=4")
    BizUserDTO GetUser();

    @Select("select * from biz_vip_record")
    BizVipRecordDTO GetVipRecord();
}
